package com.itany.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * Author:shixiaojun@itany.com
 * Date:2022/11/14 9:01
 * Version:1.0-SNAPSHOT
 * Description:
 */
public class Test08 {

    public static void main(String[] args) {
        List<Emp> emps = selectAll();
        for(Emp emp : emps){
            System.out.println(emp);
        }

        Dept dept = selectByDeptId(9);
        System.out.println(dept);
    }

    /**
     * 查询所有员工以及该员工所属的部门信息
     * 多对一的关系
     * @return
     */
    public static List<Emp> selectAll(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Emp> emps = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select e.id,e.name,e.salary,d.id 'd.id',d.name 'd.name',d.remark ")
                    .append(" from t_emp e ")
                    .append(" left join t_dept d")
                    .append(" on e.dept_id = d.id")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                Emp emp = new Emp();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setSalary(rs.getDouble("salary"));
                // 将与dept表相关的数据映射到Dept对象中
                Dept dept = new Dept();
                dept.setId(rs.getInt("d.id"));
                dept.setName(rs.getString("d.name"));
                dept.setRemark(rs.getString("remark"));
                // 将映射的部门对象通过set方法赋值给Emp中的dept对象属性
                emp.setDept(dept);
                emps.add(emp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return emps;
    }

    /**
     * 根据部门编号查询部门以及该部门中所有员工信息
     * 一对多的关系
     * @param id
     * @return
     */
    public static Dept selectByDeptId(Integer id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Dept dept = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select d.id,d.name,d.remark, ")
                    .append(" e.id 'e.id',e.name 'e.name',e.salary ")
                    .append(" from t_dept d ")
                    .append(" left join t_emp e")
                    .append(" on d.id = e.dept_id ")
                    .append(" where d.id = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while(rs.next()){
                // 当第一次进来,对dept进行映射
                // 当dept为null时,表示尚未映射,属于第一次映射
                // 映射过之后dept的值将不为null
                if(null == dept) {
                    dept = new Dept();
                    dept.setId(rs.getInt("id"));
                    dept.setName(rs.getString("name"));
                    dept.setRemark(rs.getString("remark"));
                }
                Emp emp = new Emp();
                emp.setId(rs.getInt("e.id"));
                emp.setName(rs.getString("e.name"));
                emp.setSalary(rs.getDouble("salary"));
                dept.addEmp(emp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return dept;
    }
}
